package com.hsit.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.mysql.jdbc.Statement;

@Repository
public class DogDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	
	
	//select(case when left(dname,1)='d' then CONCAT('p',right(dname,LENGTH(dname)-1)) else dname end ) as result from dog;
	public void register(String username, String password, String email) {
		String sql = "insert into user(username,password,email) values(?,?,?);";
		jdbcTemplate.update(sql, new Object[] { username, password, email });
	}

	public Object typeId(String dtypename) {
		String sql = "select dtypeid from type where dtypename=?";
		return jdbcTemplate.update(sql, new Object[] { dtypename },
				Integer.class);
	}

	public void dogregister(int dtype, String dname, String dage,
			String dprice, String ds, String dimg, String dstatus) {
		String sql = "insert into dog(dtype,dname,dage,dprice,ds,dimg,dstatus) values(?,?,?,?,?,?,?);";
		jdbcTemplate.update(sql, new Object[] { dtype, dname, dage, dprice, ds,
				dimg, dstatus });
	}

	public Object logincheck(String username, String password) {
		String sql = "select count(*) from user where username=? and password=?;";
		return jdbcTemplate.queryForObject(sql, new Object[] { username,
				password }, Integer.class);
	}

	public Object imgcheck(String dimg) {
		String sql = "select count(*) from dog where dimg=?;";
		return jdbcTemplate.queryForObject(sql, new Object[] { dimg },
				Integer.class);
	}

	public Object checktwice(String username) {
		String sql = "select count(*) from user where username=?;";
		return jdbcTemplate.queryForObject(sql, new Object[] { username },
				Integer.class);
	}

	public PageObj selectDog(int id, String dname, int dtypeid) {
		int limit = 2;// 每页的记录数
		int start = (id - 1) * limit;// 当前页第一条数据在List中的位置,从0开始
		// StringBuffer sql=new StringBuffer("select * from dog where 1=1");
		StringBuffer sql = new StringBuffer(
				"SELECT * from dog D LEFT JOIN type T ON T.dtypeid=D.dtype WHERE 1=1 ");
		// SELECT D.* from type T LEFT JOIN dog D ON T.dtypeid=D.dtype WHERE 1=1
		// and T.dtypename='柯基' and D.dname LIKE '2';
		if (dname != null && !"".equals(dname)) {
			sql.append(" and D.dname LIKE '%" + dname + "'");
		}
		if (dtypeid > 0) {
			sql.append(" and T.dtypeid='" + dtypeid + "'");
		}
		sql.append(" limit " + start + "," + limit + "");
		List list = jdbcTemplate.queryForList(sql.toString()); // 当前页中存放的记录,类型一般为List
		// StringBuffer sql1=new
		// StringBuffer("select count(*) from dog where 1=1 ");
		StringBuffer sql1 = new StringBuffer(
				"SELECT count(*) from dog D LEFT JOIN type T ON T.dtypeid=D.dtype WHERE 1=1 ");
		// SELECT count(*) from type T LEFT JOIN dog D ON T.dtypeid=D.dtype
		// WHERE 1=1
		if (dname != null && !"".equals(dname)) {
			sql1.append(" and dname LIKE '%" + dname + "';");
		}
		if (dtypeid > 0) {
			sql1.append(" and T.dtypeid='" + dtypeid + "'");
		}
		int totalCount = jdbcTemplate.queryForObject(sql1.toString(),
				Integer.class);// totalCount
		PageObj obj = new PageObj(limit, start, list, totalCount);
		return obj;
	}

	public List selectTyname() {
		String sql = "select * from type;";
		return jdbcTemplate.queryForList(sql);
	}

	public void Cariadd(int id, int did) {
		String sql = "insert into cari(id,did) values (?,?);";
		jdbcTemplate.update(sql, new Object[] { id, did });
	}

	public Object selectId(String username) {
		String sql = "select id from user where username=?;";
		return jdbcTemplate.queryForObject(sql, new Object[] { username },
				Integer.class);
	}

	public Object Caricheck(int id, int did) {
		String sql = "select count(*) from cari where id=? and did=?;";
		return jdbcTemplate.queryForObject(sql, new Object[] { id, did },
				Integer.class);
	}

	public List SelectCari(int id) {
		String sql = "select D.* from dog D LEFT JOIN cari C ON D.did=C.did WHERE C.id=?;";
		return jdbcTemplate.queryForList(sql, new Object[] { id });
	}

	public void deleteCari(String ids) {
		String sql = "delete from cari where did in (" + ids + ")";
		jdbcTemplate.update(sql);

	}

	public List seletDname(String ids) {
		String sql = "SELECT dname from dog where did in (" + ids + ");";
		return jdbcTemplate.queryForList(sql);
	}

	public int YorderAdd(final String yaddress, final String yprice,
			final String ystatus, final int id) {
		final String sql = "insert into yorder(yaddress,yprice,ystatus,id) values(?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {

			
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, yaddress);
				ps.setString(2, yprice);
				ps.setString(3, ystatus);
				ps.setInt(4, id);
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();

	}

	public void DetailAdd(int yid, int did, int count) {
		String sql = "insert into detail(yid,did,count) values(?,?,?);";
		jdbcTemplate.update(sql, new Object[] { yid, did, count });
	}
	
	
	//查询指定用户
	public List SelectStatus1(String ystatus,int id){
		String sql="SELECT yid FROM yorder where ystatus=? and id=?;";
		return jdbcTemplate.queryForList(sql, new Object[]{ystatus,id});
		
	}
	//查询    yid对应的物品名字数组
	public List SelectStatus1Dname(int yid){
		String sql="SELECT G.dname from dog G LEFT JOIN detail D ON G.did=D.did WHERE D.yid=?;";
		return jdbcTemplate.queryForList(sql, new Object[]{yid});
	}
	//查询 yid对应的物品的数量数组
	public List SelectStatus1Count(int yid){
		String sql="SELECT count FROM detail WHERE yid=?;";
		return jdbcTemplate.queryForList(sql, new Object[]{yid});
	}
	
	public List  SelectYorder(int id,String ystatus){
		String sql="SELECT * from yorder WHERE id=? AND ystatus=?;";
		return jdbcTemplate.queryForList(sql, new Object[]{id,ystatus});
	}
	public void DeleteYorder(String yids){
		String sql="delete from yorder where yid in("+yids+");";
		jdbcTemplate.update(sql);
	}
	
	public void DeleteDetail(String yids){
		String sql="delete from detail where yid in("+yids+");";
		jdbcTemplate.update(sql);
	}
	
	public void ChangeStatus(String yaddress,String yids){
		String sql="update yorder set ystatus='2',yaddress=? where yid in("+yids+");";
		jdbcTemplate.update(sql, new Object[]{yaddress});
	}
	
	public void ChangeDstatus(String dstatus,int did){
		String sql="update dog set dstatus=? where did=?;";
		jdbcTemplate.update(sql, new Object[]{dstatus,did});
	}
	public void delete1(int did){
		String sql="delete from cari where did=?;";
		jdbcTemplate.update(sql, new Object[]{did});
	}
	public List selectDetailYid(int did){
		String sql="select yid from detail where did=?;";
		return jdbcTemplate.queryForList(sql, new Object[]{did});
	}
	
	public Object selectYorderYstatus(int yid){
		String sql="SELECT ystatus from yorder WHERE yid=?;";
		return jdbcTemplate.queryForObject(sql, new Object[]{yid}, String.class);
	}
	public void deleteYorderAll(int yid){
		String sql="delete from yorder where yid=?;";
		jdbcTemplate.update(sql, new Object[]{yid});
	}
	public void deleteDetailAll(int yid){
		String sql="delete from detail where yid=?;";
		jdbcTemplate.update(sql, new Object[]{yid});
	}
	
	public Object select2(int yid){
		String sql="select count(*) from detail where yid=?;";
		return jdbcTemplate.queryForObject(sql, new Object[]{yid}, Integer.class);
	}
}
